package modle.DAO;
import modle.Bean.Employee;
import modle.Bean.InfoUtil;
import modle.Bean.User;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

public class UserDao {

    InfoUtil infoUtil = new InfoUtil();
    public UserDao() {

    }

    public boolean verifyUser(Connection conn, User user,String oldPassword) throws Exception {
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        try {
            // 查询用户表中是否有匹配的记录
            String sql = "SELECT COUNT(*) FROM user WHERE username=? AND password=?";
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, user.getUsername());
            pstmt.setString(2, oldPassword);
            rs = pstmt.executeQuery();
            if (rs.next()) {
                int count = rs.getInt(1);
                return count > 0;
            }
            return false;
        } catch (Exception e) {
            e.printStackTrace();
            return false;
        }
    }
    public User login(Connection conn, User user) throws Exception {
        User resultUser = null;
        // sql 查询语句
        String sql = "select * from user,role where user.role_id = role.role_id  and role.role_code = ? and username=? and password = ?";
        // 获得执行sql语句的对象  将查询到的值
        PreparedStatement pstatement = conn.prepareStatement(sql);
        pstatement.setString(1,user.getRoletype());
        pstatement.setString(2, user.getUsername());
        pstatement.setString(3, user.getPassword());
        // 执行sql语句获得结果集
        ResultSet rs = pstatement.executeQuery();
        if (rs.next()) {
            resultUser = new User();
            resultUser.setRoletype(rs.getString("role_code"));
            resultUser.setUsername(rs.getString("username"));
            resultUser.setPassword(rs.getString("password"));
        }
        return resultUser;
    }
    public void updateUserInfo(Connection conn, String username,String tel,String real_name) throws Exception {
        PreparedStatement pstmt = null;
        try {
            // 更新用户表中对应的密码
            String sql = "UPDATE user SET tel = ?, real_name = ? WHERE username=?";
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, tel);
            pstmt.setString(2, real_name);
            pstmt.setString(3, username);
            pstmt.executeUpdate();
        } catch (Exception e) {
            e.printStackTrace();
        }

    }

    public void updatePassword(Connection conn, User user,String newPassword) throws Exception {

        PreparedStatement pstmt = null;
        try {
            // 更新用户表中对应的密码
            String sql = "UPDATE user SET password=? WHERE username=?";
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, newPassword);
            pstmt.setString(2, user.getUsername());
            pstmt.executeUpdate();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                if (pstmt != null) {
                    pstmt.close();
                }
                if (conn != null) {
                    conn.close();
                }
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
    }
    public void deleteUser(Connection conn, String username)throws Exception {
        PreparedStatement pstmt = null;
        try {
            // 更新用户表中对应的密码
            pstmt = conn.prepareStatement("DELETE FROM user WHERE username = ?");
            pstmt.setString(1,username);
            pstmt.executeUpdate();
        } catch (Exception e) {
            e.printStackTrace();
        }

    }
    public void addUser(Connection conn, User user)throws Exception {
        PreparedStatement pstmt = null;
        try {
            pstmt = conn.prepareStatement("INSERT INTO user (username, password, role_id,regist_date,tel,real_name) VALUES (?,?,?,?,?,?)");
            pstmt.setString(1, user.getUsername());
            pstmt.setString(2, user.getPassword());
            pstmt.setString(3, getRoleCode(user.getRoletype()));
            pstmt.setString(4, user.getRegist_date());
            pstmt.setString(5, user.getTel());
            pstmt.setString(6, user.getReal_name());
            pstmt.executeUpdate();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                if (pstmt != null) {
                    pstmt.close();
                }
                if (conn != null) {
                    conn.close();
                }
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
    }
    public void addUserInfo(Connection conn, User user)throws Exception {
        PreparedStatement pstmt = null;
        try {
            // 更新用户表中对应的密码
            pstmt = conn.prepareStatement("INSERT INTO user (username, password, role_id,regist_date,tel,real_name) VALUES (?,?,?,?,?,?)");
            pstmt.setString(1, user.getUsername());
            pstmt.setString(2, user.getPassword());
            pstmt.setString(3, getRoleCode(user.getRoletype()));
            pstmt.setString(4, user.getRegist_date());
            pstmt.setString(5, user.getTel());
            pstmt.setString(6, user.getReal_name());
            pstmt.executeUpdate();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    public void employeeInfo(Connection conn ,Employee employee){
        PreparedStatement pstmt = null;
        try {

            pstmt = conn.prepareStatement("select * from employeeInfo where  username= ? ");
            pstmt.setString(1, employee.getUsername());
            ResultSet rs = pstmt.executeQuery();
            if (rs.next()) {
                employee.setName(rs.getString("real_name"));
                employee.setEmployeeID(rs.getInt("employee_id"));
                employee.setDept(rs.getString("deptname"));
                employee.setAddress(rs.getString("address"));
                employee.setAge(infoUtil.calculateAge(rs.getString("birthday")));
                employee.setBirthday(rs.getString("birthday"));
                employee.setTel(rs.getString("tel"));
                employee.setPhoto_url(rs.getString("photo_url"));
                employee.setSex(rs.getString("sex"));
                employee.setEmail(rs.getString("email"));
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                if (pstmt != null) {
                    pstmt.close();
                }
                if (conn != null) {
                    conn.close();
                }
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
    }
    public String getRoleCode(String roleType) {
        return switch (roleType) {
            case "employee" -> "1";
            case "driver" -> "2";
            case "vehicleManager" -> "3";
            case "deptAdmin" -> "4";
            case "companyAdmin" ->"5";
            default -> "-1"; // 如果输入的角色类型不在上述中，则返回-1表示非法输入
        };
    }
    public String getRoleType(int role_id) {
        return switch (role_id) {
            case 1 -> "员工";
            case 2 -> "驾驶员";
            case 3-> "车辆管理员";
            case 4-> "部门管理员";
            case 5 ->"公司管理员";
            default -> "null"; // 如果输入的角色类型不在上述中，则返回-1表示非法输入
        };
    }

    // 获取全部用户数据
    public List<User> getAllUsers(Connection conn) {
        PreparedStatement pstmt = null;
        List<User> userList = null;
        try {
            userList = new ArrayList<>();
            pstmt = conn.prepareStatement("SELECT * FROM user");
            ResultSet rs = pstmt.executeQuery();
            while (rs.next()) {
                User user = new User();
                user.setUsername(rs.getString("username"));
                user.setPassword(rs.getString("password"));
                user.setRegist_date(rs.getString("regist_date"));
                user.setTel(rs.getString("tel"));
                user.setReal_name(rs.getString("real_name"));
                user.setRoletype(getRoleType(rs.getInt("role_id")));
                userList.add(user);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                if (pstmt != null) {
                    pstmt.close();
                }
                if (conn != null) {
                    conn.close();
                }
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        return userList;
    }

    // 根据参数获取用户数据，使用PreparedStatement动态绑定参数值
    public List<User> getUsersByUsername(Connection conn,String username) {

        PreparedStatement pstmt = null;

        List<User> userList = new ArrayList<>();
        try {
            userList = new ArrayList<>();
            pstmt = conn.prepareStatement("SELECT * FROM user WHERE username=?");
            pstmt.setString(1, username);  // 绑定参数值
            ResultSet rs = pstmt.executeQuery();
            while (rs.next()) {
                User user = new User();
                user.setUsername(rs.getString("username"));
                user.setPassword(rs.getString("password"));
                user.setRegist_date(rs.getString("regist_date"));
                user.setTel(rs.getString("tel"));
                user.setReal_name(rs.getString("real_name"));
                user.setRoletype(getRoleType(rs.getInt("role_id")));
                userList.add(user);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                if (pstmt != null) {
                    pstmt.close();
                }
                if (conn != null) {
                    conn.close();
                }
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        return userList;
    }

    public List<User> getUsersByroletype(Connection conn,String roleType) {
        String role_id = getRoleCode(roleType);

        PreparedStatement pstmt = null;

        List<User> userList = new ArrayList<>();
        try {
            userList = new ArrayList<>();
            pstmt = conn.prepareStatement("SELECT * FROM user WHERE role_id=?");
            pstmt.setString(1, role_id);  // 绑定参数值
            ResultSet rs = pstmt.executeQuery();
            while (rs.next()) {
                User user = new User();
                user.setUsername(rs.getString("username"));
                user.setPassword(rs.getString("password"));
                user.setRegist_date(rs.getString("regist_date"));
                user.setTel(rs.getString("tel"));
                user.setReal_name(rs.getString("real_name"));
                user.setRoletype(getRoleType(rs.getInt("role_id")));
                userList.add(user);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                if (pstmt != null) {
                    pstmt.close();
                }
                if (conn != null) {
                    conn.close();
                }
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        return userList;
    }

    public List<User> getUsersByNameAndType(Connection conn,String userName,String roleType) {

        String role_id = getRoleCode(roleType);

        PreparedStatement pstmt = null;

        List<User> userList = new ArrayList<>();
        try {
            userList = new ArrayList<>();
            pstmt = conn.prepareStatement("SELECT * FROM user WHERE role_id=? AND username = ?");
            pstmt.setString(1, role_id);  // 绑定参数值
            pstmt.setString(2,userName);
            ResultSet rs = pstmt.executeQuery();
            while (rs.next()) {
                User user = new User();
                user.setUsername(rs.getString("username"));
                user.setPassword(rs.getString("password"));
                user.setRegist_date(rs.getString("regist_date"));
                user.setTel(rs.getString("tel"));
                user.setReal_name(rs.getString("real_name"));
                user.setRoletype(getRoleType(rs.getInt("role_id")));
                userList.add(user);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                if (pstmt != null) {
                    pstmt.close();
                }
                if (conn != null) {
                    conn.close();
                }
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        return userList;
    }

}

